﻿using Coldairarrow.Entity.Base;
using Coldairarrow.Entity.Base_Manage;
using Coldairarrow.Entity.DTO;
using Coldairarrow.Entity.IT;
using Coldairarrow.Entity.PB;
using Coldairarrow.Entity.Print;
using Coldairarrow.Entity.TD;
using Coldairarrow.IBusiness;
using Coldairarrow.IBusiness.DTO;
using Coldairarrow.Util;
using Coldairarrow.Util.ClassLibrary;
using Coldairarrow.Util.Helper;
using EFCore.Sharding;
using LinqKit;
using Microsoft.EntityFrameworkCore;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Linq.Dynamic.Core;
using System.Linq.Expressions;
using System.Text.RegularExpressions;
using System.Threading.Tasks;

namespace Coldairarrow.Business.PB
{
    public partial class PB_MaterialBusiness : BaseBusiness<PB_Material>, IPB_MaterialBusiness, ITransientDependency
    {
        readonly IOperator _operator;
        readonly IOperatorDept _operdept;
        public PB_MaterialBusiness(IDbAccessor db,
            IOperator @operator, IOperatorDept @operdept)
            : base(db)
        {
            _operator = @operator;
            _operdept = @operdept;
        }

        #region 外部接口

        public async Task<PageResult<PB_Material>> GetDataListAsync(PageInput<PBMaterialConditionDTO> input)
        {
            var q = GetIQueryable();
            var where = LinqHelper.True<PB_Material>();
            var search = input.Search;

            //筛选
            if (!search.Keyword.IsNullOrEmpty())
            {
                var newWhere = DynamicExpressionParser.ParseLambda<PB_Material, bool>(
                    ParsingConfig.Default, false, $@"Name.Contains(@0) or Code.Contains(@0) or SimpleName.Contains(@0)", search.Keyword);
                where = where.And(newWhere);
            }
            if (!search.MaterialTypeId.IsNullOrEmpty())
            {
                where = where.And(x => x.MaterialTypeId == search.MaterialTypeId);
            }
            //只显示标准件
            where = where.And(x => x.IsStandard == true);

            return await q.Where(where).GetPageResultAsync(input);
        }

        public async Task<PageResult<PB_Material>> GetDataListByPDAAsync(PageInput<PDAMaterialInputDTO> input)
        {
            var q = GetIQueryable();
            var where = LinqHelper.True<PB_Material>();
            var search = input.Search;

            //筛选
            if (!search.Code.IsNullOrEmpty())
            {
                where = where.And(x => x.Code.Contains(search.Code) || x.ProductCode.Contains(search.Code) || x.Name.Contains(search.Code) || x.Spec.Contains(search.Code));
            }
            //只显示启用的成品物料
            where = where.And(x => x.UsageStatus == true);
            where = where.And(x => x.IsProduct == true);

            return await q.Where(where).GetPageResultAsync(input);
        }
        public async Task<PageResult<PB_Material>> GetDataListByHasRefAsync(PageInput<ConditionDTO> input)
        {
            var q = GetIQueryable();
            var where = LinqHelper.True<PB_Material>();
            var search = input.Search;

            //筛选
            if (!search.Condition.IsNullOrEmpty() && !search.Keyword.IsNullOrEmpty())
            {
                var newWhere = DynamicExpressionParser.ParseLambda<PB_Material, bool>(
                    ParsingConfig.Default, false, $@"{search.Condition}==@0", search.Keyword);
                where = where.And(newWhere);
            }

            return await q.Where(where).GetPageResultAsync(input);
        }
        public async Task<PageResult<PB_MaterialStockNumberDTO>> ThroughMaterialGetDataList(PageInput<QueryPBMaterialType> input)
        {
            string sql = "";

            if (!string.IsNullOrEmpty(input.Search.MaterialId))
            {
                sql = @"   SELECT  a.*,IFNULL(g.StockNumber,0) StockNumber,g.LocalId LocationId,	g.LocationName,
g.RackName,a.ProductCode,g.StorAreaName,0 ShouldNumber
	 FROM   PB_Material a
						 LEFT JOIN	
						(
							SELECT
		a.Id,
		m.`Code`,
		m.`Spec`,
		c.AreaId,
		c.StorId,
		a.LocalId,
			IFNULL(( a.Nums_N + a.Nums_O - a.Nums_R ),0) StockNumber,
		a.MaterialId MaterialId,
		m.`Name` MaterialName,
	m.`Spec` MaterialSpec,
		c.`Name` LocationName,
		d.`Name` StorageName,
		e.`Name` StorAreaName ,
		rack.`Name` RackName
	FROM
		IT_LocalMaterial a
		INNER JOIN PB_Material m ON a.MaterialId = m.Id
		LEFT JOIN PB_Location c ON a.LocalId = c.id
		LEFT JOIN ( SELECT Id, `Name` FROM Base_Department WHERE Type = 2 ) d ON c.StorId = d.id
		LEFT JOIN PB_StorArea e ON c.AreaId = e.id 
			LEFT JOIN PB_Rack rack ON c.RackId = rack.id 

						)
							g on a.Id=g.MaterialId
								 WHERE   a.Id!='" + input.Search.MaterialId + "' ";
                //物料同类别条件限制去除 2022-03-07 朱柏清
//                a.MaterialTypeId
//IN (
//	SELECT  MaterialTypeId FROM PB_Material  WHERE Id='" + input.Search.MaterialId + "') 
                if (!string.IsNullOrEmpty(input.Search.PBMaterialName))
                {
                    sql += "  and  a.`Name` LIKE '%" + input.Search.PBMaterialName + "%' ";
                }
                if (!string.IsNullOrEmpty(input.Search.PBMaterialCode))
                {
                    sql += "  and  a.`Code` = '" + input.Search.PBMaterialCode + "' ";
                }
            }
            else
            {
                return null;
            }

            var res = await Db.GetListBySqlAsync<PB_MaterialStockNumberDTO>(sql);
            #region   分页处理数据   有bug 没有按照主数据进行处理
            //进行数据分页
            //    public int pagesize = 12;//每一页显示数据的条数，在这里让每一页显示12条
            //public int pageindex;//记录当前的页数为第几页
            int pagecount;//总页数
            List<PB_MaterialStockNumberDTO> listpro_down = res.ToList();//一个要分页的，类型数据集合，存储的是所有的数据
            List<PB_MaterialStockNumberDTO> productes = new List<PB_MaterialStockNumberDTO>();//用来存储要展示的数据
            int pagees = listpro_down.Count;//所有数据的总条数
                                            //计算总页数
            if (pagees % input.PageRows == 0)
            {
                pagecount = pagees / input.PageRows;
            }
            else
            {
                pagecount = (pagees / input.PageRows) + 1;
            }
            productes = listpro_down.Skip((input.PageIndex - 1) * input.PageRows).Take(input.PageRows).ToList();//分页的重点
            return new PageResult<PB_MaterialStockNumberDTO> { Data = productes, Total = pagees };
            #endregion
        }
        public async Task<List<PB_Material>> GetDataListAsync()
        {
            var q = GetIQueryable();

            return await q.ToListAsync();
        }



        [DataAddLog(UserLogType.物料管理, "Name", "物料")]
        [DataRepeatValidate(new string[] { "Code", "ProductCode" }, new string[] { "编号","成品编码" })]
        [Transactional]
        public async Task AddDataAsync(PB_MaterialProductDTO data, RedisOptions _redisOptions)
        {
          
            string QrCode = "04," + data.MaterialTypeId + "," + _operator.Property.DepartmentId + "," + data.Id + "," + data.CreateTime?.ToString("yyMMdd");
            string num = string.Empty;
            if (data.IsNumber == true)
            {
                //实例化Redis操作类
                _redisOptions.DbChannel = (int)Redis_Type.redis6;
                RedisSetHelper redis = new RedisSetHelper(_redisOptions);

                if (RedisSetHelper.Exists("MaterialSerialNumber"))
                {
                    if (Convert.ToInt32(redis.GetString("MaterialSerialChoice")) == 0 && redis.GetString("MaterialSerialNumber") != "99999")
                    {
                        redis.SetString("MaterialSerialNumber", (Convert.ToInt32(redis.GetString("MaterialSerialNumber")) + 1).ToString());
                        num = redis.GetString("MaterialSerialNumber").PadLeft(5, '0');
                    }
                    else if (Convert.ToInt32(redis.GetString("MaterialSerialChoice")) == 0 && redis.GetString("MaterialSerialNumber") == "99999")
                    {
                        redis.SetString("MaterialSerialNumber", "1");
                        redis.SetString("MaterialSerialChoice", (Convert.ToInt32(redis.GetString("MaterialSerialChoice")) + 1).ToString());
                        string KeyChoice = redis.GetString("MaterialSerialChoice");
                        num = redis.GetHashIn("MaterialSerialAZ", KeyChoice) + redis.GetString("MaterialSerialNumber").PadLeft(5, '0');
                    }
                    else if (Convert.ToInt32(redis.GetString("MaterialSerialChoice")) != 0 && redis.GetString("MaterialSerialNumber") != "99999")
                    {
                        string KeyChoice = redis.GetString("MaterialSerialChoice");
                        num = redis.GetHashIn("MaterialSerialAZ", KeyChoice) + redis.GetString("MaterialSerialNumber").PadLeft(5, '0');
                    }
                    else if (Convert.ToInt32(redis.GetString("MaterialSerialChoice")) != 0 && redis.GetString("MaterialSerialNumber") == "99999")
                    {
                        redis.SetString("MaterialSerialNumber", "1");
                        redis.SetString("MaterialSerialChoice", (Convert.ToInt32(redis.GetString("MaterialSerialChoice")) + 1).ToString());
                        string KeyChoice = redis.GetString("MaterialSerialChoice");
                        num = redis.GetHashIn("MaterialSerialAZ", KeyChoice) + redis.GetString("MaterialSerialNumber").PadLeft(5, '0');
                    }
                }
                else
                {
                    redis.SetString("MaterialSerialNumber", "0");
                    redis.SetString("MaterialSerialNumber", (Convert.ToInt32(redis.GetString("MaterialSerialNumber")) + 1).ToString());
                    num = redis.GetString("MaterialSerialNumber").PadLeft(5, '0');
                }
                QrCode += "," + num;
            }
            data.QrCode = QrCode;
            data.IsBuy = false;
            data.HasRef = false;
            data.IsCruxMaterial = false;
            data.IsKeyprice = false;//
            data.IsStandard = false;
            data.UsageStatus = true;
            #region  查询登录人最上级的组织id
            string deptId = string.Empty;
            var deptdata = await Db.GetIQueryable<Base_Department>().Where(x => x.Id == _operator.Property.DepartmentId).FirstOrDefaultAsync();
            if (deptdata.Type == 1)
            {
                deptId = deptdata.Id;
            }
            else
            {
                if (deptId.IsNullOrEmpty())
                {
                    string parentId = deptdata.ParentId;
                    do
                    {
                        var ddata = await Db.GetIQueryable<Base_Department>().Where(x => x.Id == parentId).FirstOrDefaultAsync();
                        if (ddata.Type != 1)
                        {
                            parentId = ddata.ParentId;
                        }
                        else
                        {
                            deptId = ddata.Id;
                        }
                    } while (deptId == "");
                }
            }
            #endregion
            data.DeptId = deptId;
            List<PB_MaterialProduct> listdata = new List<PB_MaterialProduct>();
            foreach (var item in data.MaterialProductIds)
            {
                PB_MaterialProduct mp = new PB_MaterialProduct();
                mp.Id = IdHelper.GetId();
                mp.MaterialId = data.Id;
                mp.SubordinateProduct = item;
                listdata.Add(mp);
            }
            await Db.InsertAsync<PB_MaterialProduct>(listdata);

            await InsertAsync(data);
        }

        [DataEditLog(UserLogType.物料管理, "Name", "物料")]
        [DataRepeatValidate(new string[] { "Code" }, new string[] { "编号" })]
        [Transactional]
        public async Task UpdateDataAsync(PB_MaterialProductDTO data, RedisOptions _redisOptions)
        {
           
            string QrCode = "04," + data.MaterialTypeId + "," + _operator.Property.DepartmentId + "," + data.Id + "," + data.CreateTime?.ToString("yyMMdd");
            string num = string.Empty;
            data.Code = (data.Code).Trim();
            data.ProductCode = data.ProductCode == null ? "" : (data.ProductCode).Trim();
            if (data.IsNumber == true)
            {
                //实例化Redis操作类
                _redisOptions.DbChannel = (int)Redis_Type.redis6;
                RedisSetHelper redis = new RedisSetHelper(_redisOptions);

                if (RedisSetHelper.Exists("MaterialSerialNumber"))
                {
                    if (Convert.ToInt32(redis.GetString("MaterialSerialChoice")) == 0 && redis.GetString("MaterialSerialNumber") != "99999")
                    {
                        redis.SetString("MaterialSerialNumber", (Convert.ToInt32(redis.GetString("MaterialSerialNumber")) + 1).ToString());
                        num = redis.GetString("MaterialSerialNumber").PadLeft(5, '0');
                    }
                    else if (Convert.ToInt32(redis.GetString("MaterialSerialChoice")) == 0 && redis.GetString("MaterialSerialNumber") == "99999")
                    {
                        redis.SetString("MaterialSerialNumber", "1");
                        redis.SetString("MaterialSerialChoice", (Convert.ToInt32(redis.GetString("MaterialSerialChoice")) + 1).ToString());
                        string KeyChoice = redis.GetString("MaterialSerialChoice");
                        num = redis.GetHashIn("MaterialSerialAZ", KeyChoice) + redis.GetString("MaterialSerialNumber").PadLeft(5, '0');
                    }
                    else if (Convert.ToInt32(redis.GetString("MaterialSerialChoice")) != 0 && redis.GetString("MaterialSerialNumber") != "99999")
                    {
                        string KeyChoice = redis.GetString("MaterialSerialChoice");
                        num = redis.GetHashIn("MaterialSerialAZ", KeyChoice) + redis.GetString("MaterialSerialNumber").PadLeft(5, '0');
                    }
                    else if (Convert.ToInt32(redis.GetString("MaterialSerialChoice")) != 0 && redis.GetString("MaterialSerialNumber") == "99999")
                    {
                        redis.SetString("MaterialSerialNumber", "1");
                        redis.SetString("MaterialSerialChoice", (Convert.ToInt32(redis.GetString("MaterialSerialChoice")) + 1).ToString());
                        string KeyChoice = redis.GetString("MaterialSerialChoice");
                        num = redis.GetHashIn("MaterialSerialAZ", KeyChoice) + redis.GetString("MaterialSerialNumber").PadLeft(5, '0');
                    }
                }
                else
                {
                    redis.SetString("MaterialSerialNumber", "0");
                    redis.SetString("MaterialSerialNumber", (Convert.ToInt32(redis.GetString("MaterialSerialNumber")) + 1).ToString());
                    num = redis.GetString("MaterialSerialNumber").PadLeft(5, '0');
                }
                QrCode += "," + num;
            }
            data.QrCode = QrCode;
            #region  查询登录人最上级的组织id
            string deptId = string.Empty;
            var deptdata = await Db.GetIQueryable<Base_Department>().Where(x => x.Id == _operator.Property.DepartmentId).FirstOrDefaultAsync();
            if (deptdata.Type == 1)
            {
                deptId = deptdata.Id;
            }
            else
            {
                if (deptId.IsNullOrEmpty())
                {
                    string parentId = deptdata.ParentId;
                    do
                    {
                        var ddata = await Db.GetIQueryable<Base_Department>().Where(x => x.Id == parentId).FirstOrDefaultAsync();
                        if (ddata.Type != 1)
                        {
                            parentId = ddata.ParentId;
                        }
                        else
                        {
                            deptId = ddata.Id;
                        }
                    } while (deptId == "");
                }
            }
            #endregion
            data.DeptId = deptId;
            data.UsageStatus = data.UsageStatus;
            await Db.ExecuteSqlAsync("DELETE FROM `PB_MaterialProduct` where MaterialId='" + data.Id + "'");

            List<PB_MaterialProduct> listdata = new List<PB_MaterialProduct>();
            foreach (var item in data.MaterialProductIds)
            {
                PB_MaterialProduct mp = new PB_MaterialProduct();
                mp.Id = IdHelper.GetId();
                mp.MaterialId = data.Id;
                mp.SubordinateProduct = item;
                listdata.Add(mp);
            }
            await Db.InsertAsync<PB_MaterialProduct>(listdata);

            await UpdateAsync(data);

        }

        [DataDeleteLog(UserLogType.物料管理, "Name", "物料")]
        public async Task DeleteDataAsync(List<string> ids)
        {
            if (await  Db.GetIQueryable<PB_PickUpList>().AnyAsync(x => ids.Contains(x.MaterialId)))
              throw new BusException("禁止删除!备货单有该物料正在备货！");
            if (await Db.GetIQueryable<TD_RecDetail>().AnyAsync(x => ids.Contains(x.MaterialId)))
                throw new BusException("禁止删除!收货单有该物料禁止删除！");
            if (await Db.GetIQueryable<IT_LocalMaterial>().AnyAsync(x => ids.Contains(x.MaterialId)))
                throw new BusException("禁止删除!库存报表有该物料信息！");
            if (await Db.GetIQueryable<IT_LocalDetail>().AnyAsync(x => ids.Contains(x.MaterialId)))
                throw new BusException("禁止删除!库存明细有该物料信息！");
            if (await Db.GetIQueryable<PB_DesignDetail>().AnyAsync(x => ids.Contains(x.MaterialId)))
                throw new BusException("禁止删除!设计清单有该物料信息！");
            if (await Db.GetIQueryable<PB_ProductionOrderDetail>().AnyAsync(x => ids.Contains(x.MaterialId)))
                throw new BusException("禁止删除!生产任务单有该物料信息！");
            if (await Db.GetIQueryable<PB_DispatchDetail>().AnyAsync(x => ids.Contains(x.MaterialId)))
                throw new BusException("禁止删除!派工单有该物料信息！");
            if (await Db.GetIQueryable<PB_OutsourcingBillDetail>().AnyAsync(x => ids.Contains(x.MaterialId)))
                throw new BusException("禁止删除!外协加工有该物料信息！");
            if (await Db.GetIQueryable<PB_ReturnDetail>().AnyAsync(x => ids.Contains(x.MaterialId)))
                throw new BusException("禁止删除!回货有该物料信息！");
            if (await Db.GetIQueryable<PB_TransportBillDetail>().AnyAsync(x => ids.Contains(x.MaterialId)))
                throw new BusException("禁止删除!该物料信息已有装车数据！");
            await DeleteAsync(ids);
        }

        public async Task AddDataExlAsync(List<PB_Material> list, RedisOptions _redisOptions, MongoDBOptions _mongodbOptions)
        {
            //await InsertAsync(list);
            string QrCode = string.Empty;
            string num = string.Empty;
            //实例化Redis操作类
            _redisOptions.DbChannel = (int)Redis_Type.redis6;
            RedisSetHelper redis = new RedisSetHelper(_redisOptions);
            var deptdata = await _operdept.GetOperatorDept();
            string deptId = deptdata.DetpId;
            foreach (var item in list)
            {
                QrCode = "04," + item.MaterialTypeId + "," + _operator.Property.DepartmentId + "," + item.Id + "," + DateTime.Now.ToString("yyMMdd");
                //if (item.IsNumber == true)
                //{
                //    #region  序列号取值
                //    if (RedisSetHelper.Exists("MaterialSerialNumber"))
                //    {
                //        if (Convert.ToInt32(redis.GetString("MaterialSerialChoice")) == 0 && redis.GetString("MaterialSerialNumber") != "99999")
                //        {
                //            redis.SetString("MaterialSerialNumber", (Convert.ToInt32(redis.GetString("MaterialSerialNumber")) + 1).ToString());
                //            num = redis.GetString("MaterialSerialNumber").PadLeft(5, '0');
                //        }
                //        else if (Convert.ToInt32(redis.GetString("MaterialSerialChoice")) == 0 && redis.GetString("MaterialSerialNumber") == "99999")
                //        {
                //            redis.SetString("MaterialSerialNumber", "1");
                //            redis.SetString("MaterialSerialChoice", (Convert.ToInt32(redis.GetString("MaterialSerialChoice")) + 1).ToString());
                //            string KeyChoice = redis.GetString("MaterialSerialChoice");
                //            num = redis.GetHashIn("MaterialSerialAZ", KeyChoice) + redis.GetString("MaterialSerialNumber").PadLeft(5, '0');
                //        }
                //        else if (Convert.ToInt32(redis.GetString("MaterialSerialChoice")) != 0 && redis.GetString("MaterialSerialNumber") != "99999")
                //        {
                //            string KeyChoice = redis.GetString("MaterialSerialChoice");
                //            num = redis.GetHashIn("MaterialSerialAZ", KeyChoice) + redis.GetString("MaterialSerialNumber").PadLeft(5, '0');
                //        }
                //        else if (Convert.ToInt32(redis.GetString("MaterialSerialChoice")) != 0 && redis.GetString("MaterialSerialNumber") == "99999")
                //        {
                //            redis.SetString("MaterialSerialNumber", "1");
                //            redis.SetString("MaterialSerialChoice", (Convert.ToInt32(redis.GetString("MaterialSerialChoice")) + 1).ToString());
                //            string KeyChoice = redis.GetString("MaterialSerialChoice");
                //            num = redis.GetHashIn("MaterialSerialAZ", KeyChoice) + redis.GetString("MaterialSerialNumber").PadLeft(5, '0');
                //        }
                //    }
                //    else
                //    {
                //        redis.SetString("MaterialSerialNumber", "0");
                //        redis.SetString("MaterialSerialChoice", (Convert.ToInt32(redis.GetString("MaterialSerialChoice")) + 1).ToString());
                //        num = redis.GetString("MaterialSerialNumber").PadLeft(5, '0');
                //    }
                //    QrCode += "," + num;
                //    #endregion
                //}
                item.QrCode = QrCode;
                item.CreateTime = DateTime.Now;

                item.DeptId = deptId;
            }
            //MongoDBHelper mongo = new MongoDBHelper(_mongodbOptions);
            //ErrLog err = new ErrLog()
            //{
            //    Name = "物料导入",
            //    Interface = "/PB/PB_Material/Import",
            //    Type = "错误",
            //    Time = DateTime.Now,
            //    Remark = list.Count().ToString(),
            //};
            //mongo.Add<ErrLog>(ErrLogCollection, err);
            try
            {
                //Db.BulkInsert<PB_Material>(list);

                await InsertAsync(list); 
            }
            catch (Exception ex)
            {
                //throw new BusException(ex.Message);
                throw new BusException("上传失败！");
            }

        }

        public IQueryable<T> GetQueryable<T>() where T : class, new()
        {
            return Db.GetIQueryable<T>();
        }

        public List<T> GetQueryableByProductCode<T>(List<string> productCodes) where T : class, new()
        {
            string where = "";
            foreach (var item in productCodes)
            {
                where += "'"+item+"',";
            }
            return Db.GetListBySql<T>("select Id,ProductCode from PB_Material where ProductCode in (" + where.Substring(0, where.Length - 1) + ") and UsageStatus=1 ");
        }


        public List<T> GetQueryableByProductUsageStatusCode<T>(List<string> productCodes) where T : class, new()
        {
            string where = "";
            foreach (var item in productCodes)
            {
                where += "'" + item + "',";
            }
            return Db.GetListBySql<T>("select Id,ProductCode from PB_Material where ProductCode in (" + where.Substring(0, where.Length - 1) + ") and UsageStatus=1");
        }

        public List<T> GetUnitItems<T>(List<string> UnitItems) where T : class, new()
        {
            string where = "";
            foreach (var item in UnitItems)
            {
                where += "'" + item + "',";
            }
            return Db.GetListBySql<T>("select * from Base_UnitItems where `Name` IN (" + where.Substring(0, where.Length - 1) + ");");
        }

        public void AddSetRedis()
        {
            new RedisOptions().DbChannel = 8;
            RedisSetHelper redis = new RedisSetHelper(new RedisOptions());
            RedisSetHelper.KeyDelete("BaseUnitGroupRedis");
            var UnitGroup = Db.GetListBySql<Base_UnitGroup>("SELECT CONCAT_WS('_',GroupId,Name) as Id,Id as `Name`   from  Base_UnitItems where`Deleted`=FALSE");
            Dictionary<string, string> TagSet = new Dictionary<string, string>();
            foreach (var item in UnitGroup)
            {
                TagSet.Add(item.Id, item.Name);//
            }
            RedisSetHelper.SetHash("BaseUnitGroupRedis", TagSet);
        }

        public async Task<PageResult<PB_MaterialListData>> GetDataSelectAsync(PageInput<ConditionDTO> input)
        {
            var search = input.Search;
            Expression<Func<PB_Material, PB_ProcecureMain, PB_MaterialListData>> select = (a, b) => new PB_MaterialListData
            {
                ProcecureName = b.Name

            };
            select = select.BuildExtendSelectExpre();
            var q = from a in GetIQueryable().AsExpandable()
                    join b in Db.GetIQueryable<PB_ProcecureMain>() on a.ProcecureId equals b.Id into ab
                    from b in ab.DefaultIfEmpty()
                    select @select.Invoke(a, b);
            if (!search.Keyword.IsNullOrEmpty())
            {
                var keyword = $"%{search.Keyword}%";
                q = q.Where(x =>
                      EF.Functions.Like(x.Name, keyword)
                      || EF.Functions.Like(x.Code, keyword));
            }
            return await q.OrderBy(x => x.Code).GetPageResultAsync(input);
        }

        /// <summary>
        /// 生产任务单根据设计清单选择物料信息
        /// </summary>
        /// <param name="input"></param>
        /// <param name="ParentId">设计清单id</param>
        /// 增加搜索条件是否成品和是否条码管理   2022-02-21  赵锦涛
        /// <returns></returns>
        public async Task<PageResult<PB_MaterialDataSelectList>> GetDataSelectListAsync(PageInput<PBMaterialConditionDTO> input, string ParentId)
        {
            var search = input.Search;
            Expression<Func<PB_Material, PB_MaterialType, PB_DesignDetail, PB_DesignList, PB_MaterialDataSelectList>> select = (a, b, c, d) => new PB_MaterialDataSelectList
            {
                TypeName = b.Name,
                ProcecureMainId = d.Id,
                ProcecureMainName = d.Name,
                Nums = c.Nums
            };
            select = select.BuildExtendSelectExpre();
            var q = from a in GetIQueryable().AsExpandable()
                    join b in Db.GetIQueryable<PB_MaterialType>() on a.MaterialTypeId equals b.Id into ab
                    from b in ab.DefaultIfEmpty()
                    join c in Db.GetIQueryable<PB_DesignDetail>() on a.Id equals c.MaterialId into ac
                    from c in ac.DefaultIfEmpty()
                    join d in Db.GetIQueryable<PB_DesignList>() on c.ParentId equals d.Id into ad
                    from d in ad.DefaultIfEmpty()
                    select @select.Invoke(a, b, c, d);
            var where = LinqHelper.True<PB_MaterialDataSelectList>();
            List<ConditionDTO> conditions = GetConditionList(input.Search);
            if (conditions.Count() > 0)
            {
                foreach (var item in conditions)
                {
                    if (item.Condition != "Keyword")
                    {
                        if (item.Condition == "IsProduct")
                        {
                            where = where.And(x => x.IsProduct == Convert.ToBoolean(item.Keyword));
                        }
                        else if (item.Condition == "IsBecode")
                        {
                            where = where.And(x => x.IsBecode == Convert.ToBoolean(item.Keyword));
                        }
                        else
                        {
                            var newWhere = DynamicExpressionParser.ParseLambda<PB_MaterialDataSelectList, bool>(
                             ParsingConfig.Default, false, $@"{item.Condition}.Contains(@0)", item.Keyword);
                            where = where.And(newWhere);
                        }
                    }
                    else
                    {
                        var newWhere = DynamicExpressionParser.ParseLambda<PB_MaterialDataSelectList, bool>(
                         ParsingConfig.Default, false, $@"Name.Contains(@0) or Code.Contains(@0)", item.Keyword);
                        where = where.And(newWhere);
                    }

                }
            }
            q = q.Where(x => x.ProcecureMainId == ParentId);
            return await q.Where(where).OrderBy(x => x.Code).GetPageResultAsync(input);
        }

        /// <summary>
        /// 筛选查询物料 .根据生产任务单ID
        /// </summary>
        /// <param name="input"></param>
        /// <param name="parentId"></param>
        /// 2022-03-18 李进宝
        /// <returns></returns>
        public async Task<PageResult<PB_MaterialDataSelectList>> GetDataSelectProductionListsAsync(PageInput<PBMaterialConditionDTO> input, string parentId)
        {
            var search = input.Search;
            Expression<Func<PB_Material, PB_MaterialType, PB_ProductionOrderDetail,PB_ProductionOrder, PB_MaterialDataSelectList>> select = (a, b, c,d) => new PB_MaterialDataSelectList
            {
                TypeName = b.Name,
                Nums = c.Nums,
                ProductionOrderId =d.Id,
                ProductionOrderName =d.POName
            };
            select = select.BuildExtendSelectExpre();
            var q = from a in GetIQueryable().AsExpandable()
                    join b in Db.GetIQueryable<PB_MaterialType>() on a.MaterialTypeId equals b.Id into ab
                    from b in ab.DefaultIfEmpty()
                    join c in Db.GetIQueryable<PB_ProductionOrderDetail>() on a.Id equals c.MaterialId into ac
                    from c in ac.DefaultIfEmpty()
                    join d in Db.GetIQueryable<PB_ProductionOrder>() on c.ParentId equals d.Id into ad
                    from d in ad.DefaultIfEmpty()
                    select @select.Invoke(a, b, c,d);
            var where = LinqHelper.True<PB_MaterialDataSelectList>();
            List<ConditionDTO> conditions = GetConditionList(input.Search);
            if (conditions.Count() > 0)
            {
                foreach (var item in conditions)
                {
                    if (item.Condition != "Keyword")
                    {
                        if (item.Condition == "IsProduct")
                        {
                            where = where.And(x => x.IsProduct == Convert.ToBoolean(item.Keyword));
                        }
                        else if (item.Condition == "IsBecode")
                        {
                            where = where.And(x => x.IsBecode == Convert.ToBoolean(item.Keyword));
                        }
                        else
                        {
                            var newWhere = DynamicExpressionParser.ParseLambda<PB_MaterialDataSelectList, bool>(
                             ParsingConfig.Default, false, $@"{item.Condition}.Contains(@0)", item.Keyword);
                            where = where.And(newWhere);
                        }
                    }
                    else
                    {
                        var newWhere = DynamicExpressionParser.ParseLambda<PB_MaterialDataSelectList, bool>(
                         ParsingConfig.Default, false, $@"Name.Contains(@0) or Code.Contains(@0)", item.Keyword);
                        where = where.And(newWhere);
                    }

                }
            }
            q = q.Where(x => x.ProductionOrderId == parentId);
            return await q.Where(where).OrderBy(x => x.Code).GetPageResultAsync(input);
        }

        public async Task<MaterialProductDTO> GetTheDataAsync(string id)
        {
            //var q = this.GetIQueryable().Include(i => i.MaterialType).Include(i => i.Measure).Include(i => i.Supplier).Include(i => i.Customer).Include(i=>i.ProcecureI).Include(i=>i.UnitGroupId);
            //var q = this.GetIQueryable();
            //return await q.SingleOrDefaultAsync(w => w.Id == id);
            PB_Material pmdata = await Db.GetIQueryable<PB_Material>().Where(x => x.Id == id).FirstOrDefaultAsync();
            pmdata.Code = pmdata.Code.Replace("\t", "");
            MaterialProductDTO mdata = new MaterialProductDTO();
            mdata.Id = pmdata.Id;
            mdata.DeptId = pmdata.DeptId;
            mdata.Name = pmdata.Name;
            mdata.Code = pmdata.Code;
            mdata.ProductCode = pmdata.ProductCode;
            mdata.BarCode = pmdata.BarCode;
            mdata.SimpleName = pmdata.SimpleName;
            mdata.MaterialTypeId = pmdata.MaterialTypeId;
            mdata.UnitGroupId = pmdata.UnitGroupId;
            mdata.storageId = pmdata.storageId;
            mdata.saleId = pmdata.saleId;
            mdata.purchaseId = pmdata.purchaseId;
            mdata.productionId = pmdata.productionId;
            mdata.ProcecureId = pmdata.ProcecureId;
            mdata.Spec = pmdata.Spec;
            mdata.Max = pmdata.Max;
            mdata.Min = pmdata.Min;
            mdata.CusId = pmdata.CusId;
            mdata.SupId = pmdata.SupId;
            mdata.Price = pmdata.Price;
            mdata.NewPrice = pmdata.NewPrice;
            mdata.MinPrice = pmdata.MinPrice;
            mdata.MaxPrice = pmdata.MaxPrice;
            mdata.SettlePrice = pmdata.SettlePrice;
            mdata.Weight = pmdata.Weight;
            mdata.Area = pmdata.Area;
            mdata.PackingSpec = pmdata.PackingSpec;
            mdata.Length = pmdata.Length;
            mdata.Width = pmdata.Width;
            mdata.Height = pmdata.Height;
            mdata.Estimate = pmdata.Estimate;
            mdata.QrCode = pmdata.QrCode;
            mdata.PositionCode = pmdata.PositionCode;
            mdata.Ofmaterial = pmdata.Ofmaterial;
            mdata.Remarks = pmdata.Remarks;
            mdata.CreateTime = pmdata.CreateTime;
            mdata.CreatorId = pmdata.CreatorId;
            mdata.Deleted = pmdata.Deleted;
            mdata.IsProduct = pmdata.IsProduct;
            mdata.IsMine = pmdata.IsMine;
            mdata.IsBuy = pmdata.IsBuy;
            mdata.HasRef = pmdata.HasRef;
            mdata.IsVirtualkey = pmdata.IsVirtualkey;
            mdata.IsLabour = pmdata.IsLabour;
            mdata.IsNumber = pmdata.IsNumber;
            mdata.IsKeyprice = pmdata.IsKeyprice;
            mdata.IsCruxMaterial = pmdata.IsCruxMaterial;
            mdata.IsQuantity = pmdata.IsQuantity;
            mdata.IsExtensiveProduction = pmdata.IsExtensiveProduction;
            mdata.IsBecode = pmdata.IsBecode;
            mdata.MaterialImg = pmdata.MaterialImg;
            mdata.PackageNum = pmdata.PackageNum;
            mdata.ScanNum = pmdata.ScanNum;
            mdata.Storagenumber = pmdata.Storagenumber;
            mdata.Salenumber = pmdata.Salenumber;
            mdata.Purchasenumber = pmdata.Purchasenumber;
            mdata.MainUnit = pmdata.MainUnit;
            mdata.IsSingle = pmdata.IsSingle;
            mdata.IsStandard = pmdata.IsStandard;
            mdata.MaxPackageNum = pmdata.MaxPackageNum;
            mdata.IsSpec = pmdata.IsSpec;
            mdata.UsageStatus = pmdata.UsageStatus;
            mdata.Remarks1 = pmdata.Remarks1;
            mdata.Remarks2 = pmdata.Remarks2;
            mdata.Remarks3 = pmdata.Remarks3;
            mdata.Remarks4 = pmdata.Remarks4;
            mdata.Remarks5 = pmdata.Remarks5;
            mdata.Remarks6 = pmdata.Remarks6;
            mdata.Remarks7 = pmdata.Remarks7;
            mdata.Remarks8 = pmdata.Remarks8;
            mdata.Remarks9 = pmdata.Remarks9;
            mdata.Remarks10 = pmdata.Remarks10;
            Expression<Func<PB_MaterialProduct, PB_Material, ProductMaterialDTO>> select = (a, b) => new ProductMaterialDTO
            {
                Id = a.SubordinateProduct,
                MaterialId = a.MaterialId,
                Name = b.Name,
                Code = b.Code,
                Spec = b.Spec,
                ProductCode = b.ProductCode
            };
            select = select.BuildExtendSelectExpre();
            var q = from a in Db.GetIQueryable<PB_MaterialProduct>().AsExpandable()
                    join b in Db.GetIQueryable<PB_Material>() on a.SubordinateProduct equals b.Id into ab
                    from b in ab.DefaultIfEmpty()
                    select @select.Invoke(a, b);
            var listdata = await q.Where(x => x.MaterialId == id).ToListAsync();
            mdata.ProductList = listdata;
            List<string> MaterialProductIds = new List<string>();
            foreach (var item in listdata)
            {
                MaterialProductIds.Add(item.Id);
            }
            mdata.MaterialProductIds = MaterialProductIds;
            return mdata;
        }
        public async Task<List<PB_Material>> GetQueryData(SelectQueryDTO search)
        {
            var q = GetIQueryable();
            var where = LinqHelper.True<PB_Material>();
            if (!search.Keyword.IsNullOrEmpty())
                where = where.And(w => w.Name.Contains(search.Keyword) || w.Code.Contains(search.Keyword) || w.BarCode.Contains(search.Keyword));

            var result = await q.Where(where).OrderBy(o => o.Name).Take(search.Take).ToListAsync();
            if (!search.Id.IsNullOrEmpty())
            {
                var one = await this.GetIQueryable().Where(w => w.Id == search.Id).SingleOrDefaultAsync();
                result.Add(one);
            }
            return result;
        }

        //public async Task<PageResult<PB_Material>> QueryDataListAsync(PageInput<PBMaterialConditionDTO> input)
        //{
        //    var q = GetIQueryable().Include(i => i.MaterialType).Include(i => i.UnitGroup).Include(i => i.ProcecureI);
        //    var where = LinqHelper.True<PB_Material>();
        //    var search = input.Search;

        //    if (!search.TypeId.IsNullOrEmpty()) where = where.And(p => p.MaterialTypeId == search.TypeId);
        //    if (!search.ContactName.IsNullOrEmpty()) where = where.And(p => p.Supplier.Name.Contains(search.ContactName) || p.Supplier.Code.Contains(search.ContactName) || p.Customer.Name.Contains(search.ContactName) || p.Customer.Code.Contains(search.ContactName));
        //    if (!search.Keyword.IsNullOrEmpty()) where = where.And(p => p.Name.Contains(search.Keyword) || p.Code.Contains(search.Keyword) || p.BarCode.Contains(search.Keyword) || p.SimpleName.Contains(search.Keyword));


        //    return await q.Where(where).GetPageResultAsync(input);
        //}

        public async Task<PageResult<PB_MaterialListDTO>> QueryDataListAsync(PageInput<PBMaterialConditionDTO> input)
        {
            var search = input.Search;
            Expression<Func<PB_Material, PB_MaterialType, Base_UnitGroup, PB_ProcecureSub, PB_MaterialListDTO>> select = (a, b, c, d) => new PB_MaterialListDTO
            {
                MaterialTypeName = b.Name,
                UnitGroupName = c.Name,
                ProcecureName = d.Name,
                Typeid = b.Id,
                MaterialTypeCode = b.Code,
                UnGroupId=c.Id


            };
            select = select.BuildExtendSelectExpre();
            var q = from a in GetIQueryable().AsExpandable()
                    join b in Db.GetIQueryable<PB_MaterialType>() on a.MaterialTypeId equals b.Id into ab
                    from b in ab.DefaultIfEmpty()
                    join c in Db.GetIQueryable<Base_UnitGroup>() on a.UnitGroupId equals c.Id into ac
                    from c in ac.DefaultIfEmpty()
                    join d in Db.GetIQueryable<PB_ProcecureSub>() on a.ProcecureId equals d.Id into ad
                    from d in ad.DefaultIfEmpty()
                    select @select.Invoke(a, b, c, d);

        

            var where = LinqHelper.True<PB_MaterialListDTO>();
            //筛选
            if (!search.Keyword.IsNullOrEmpty())
            {
                var newWhere = DynamicExpressionParser.ParseLambda<PB_MaterialListDTO, bool>(
                    ParsingConfig.Default, false, $@"Name.Contains(@0) or Code.Contains(@0) or SimpleName.Contains(@0) or Spec.Contains(@0)", search.Keyword);
                where = where.And(newWhere);
            }
            if (!search.MaterialTypeId.IsNullOrEmpty())
            {
                List<string> liststr = new List<string>();
                List<PB_MaterialType> listdata = await Db.GetIQueryable<PB_MaterialType>().ToListAsync();
                var typelist = listdata.Where(x => x.Id == search.MaterialTypeId).FirstOrDefault();
                if (typelist != null)
                {
                    var typelist2 = listdata.Where(x => x.ParentId == typelist.Id).ToList();
                    if (typelist2.Count > 0)
                    {

                        foreach (var item in typelist2)
                        {
                            liststr.Add(item.Id);
                            GetItem(item.Id, liststr, listdata);
                        }
                        q = q.Where(w => liststr.Contains(w.MaterialTypeId));
                    }
                    else
                    {
                        q = q.Where(w => w.MaterialTypeId == search.MaterialTypeId);
                    }
                }
            }
            if (!search.UnitGroupId.IsNullOrEmpty()) {
                q = q.Where(m=>m.UnitGroupId==search.UnitGroupId);
            }
            if (!search.IsBecode.IsNullOrEmpty())
            {
                var newWhere = DynamicExpressionParser.ParseLambda<PB_MaterialListDTO, bool>(
                    ParsingConfig.Default, false, $@"IsBecode==@0", search.IsBecode);
                where = where.And(newWhere);
            }
            if (!search.ProductCode.IsNullOrEmpty()) {
                var newWhere = DynamicExpressionParser.ParseLambda<PB_MaterialListDTO, bool>(
                      ParsingConfig.Default, false, $@"ProductCode.Contains(@0)", search.ProductCode);
                where = where.And(newWhere);
            }
            if (!search.ProductCode.IsNullOrEmpty())
            {
                var newWhere = DynamicExpressionParser.ParseLambda<PB_MaterialListDTO, bool>(
                      ParsingConfig.Default, false, $@"ProductCode.Contains(@0)", search.ProductCode);
                where = where.And(newWhere);
            }
            if (!search.IsProduct.IsNullOrEmpty())
            {
                var newWhere = DynamicExpressionParser.ParseLambda<PB_MaterialListDTO, bool>(
                      ParsingConfig.Default, false, $@"IsProduct==@0", search.IsProduct);
                where = where.And(newWhere);
            }
            if (!search.Spec.IsNullOrEmpty()) {
                q = q.Where(x=>x.Spec.Contains(search.Spec));
            }
            if (!search.UsageStatus.IsNullOrEmpty()) {
                q = q.Where(m=>m.UsageStatus==search.UsageStatus);
            }
            //物料信息增加数据过滤
            q = q.Where(m=>_operator.Property.DeptIds.Contains(m.DeptId));
            return await q.Where(where).GetPageResultAsync(input);
        }

        //递归查询物料类型下的子类型
        private List<string> GetItem(string typeId, List<string> liststr, List<PB_MaterialType> listdata)
        {
            var itemlist = listdata.Where(x => x.ParentId == typeId).ToList();
            foreach (var item in itemlist)
            {
                liststr.Add(item.Id);
                GetItem(item.Id, liststr, listdata);
            }
            return liststr;
        }

        public async Task<PageResult<PB_MaterialStockDTO>> GetMaterialStockDataListAsync(PageInput<PBMaterialConditionDTO> input)
        {
            var search = input.Search;
            Expression<Func<PB_Material, IT_LocalMaterial, Base_Department, PB_StorArea, PB_Rack, PB_Location, PB_MaterialStockDTO>> select = (a, b, c, d, e, f) => new PB_MaterialStockDTO
            {
                Id = b.Id,
                MaterialId = b.MaterialId,
                MaterialName = a.Name,
                MaterialCode = a.Code,
                MaterialSpec = a.Spec,
                SimpleName = a.SimpleName,
                MaterialTypeId = a.MaterialTypeId,
                Warehouse = c.Name,
                Area = d.Name,
                Rack = e.Name,
                Location = f.Name,
                Nums = b.Nums_N + b.Nums_O - b.Nums_R,
                StorId = c.Id,
                AreaId = d.Id,
                RackId = e.Id,
                LocalId = f.Id,
                IsBecode = a.IsBecode
            };
            select = select.BuildExtendSelectExpre();
            //var q = from a in Db.GetIQueryable<PB_Stock>()
            //        join b in Db.GetIQueryable<PB_Material>() on a.MaterialId equals b.Id into ab
            //        from b in ab.DefaultIfEmpty()
            //        join c in Db.GetIQueryable<Base_Department>() on a.StorId equals c.Id into ac
            //        from c in ac.DefaultIfEmpty()
            //        join d in Db.GetIQueryable<PB_StorArea>() on a.AreaId equals d.Id into ad
            //        from d in ad.DefaultIfEmpty()
            //        join e in Db.GetIQueryable<PB_Rack>() on a.RackId equals e.Id into ae
            //        from e in ae.DefaultIfEmpty()
            //        join f in Db.GetIQueryable<PB_Location>() on a.LocationId equals f.Id into af
            //        from f in af.DefaultIfEmpty()
            //        select @select.Invoke(a, b, c, d, e, f);
            var q = from a in GetIQueryable().AsExpandable()
                    join b in Db.GetIQueryable<IT_LocalMaterial>() on a.Id equals b.MaterialId into ab
                    from b in ab.DefaultIfEmpty()
                    join c in Db.GetIQueryable<Base_Department>() on b.StorId equals c.Id into bc
                    from c in bc.DefaultIfEmpty()
                    join d in Db.GetIQueryable<PB_StorArea>() on b.AreaId equals d.Id into bd
                    from d in bd.DefaultIfEmpty()
                    join e in Db.GetIQueryable<PB_Rack>() on b.RackId equals e.Id into be
                    from e in be.DefaultIfEmpty()
                    join f in Db.GetIQueryable<PB_Location>() on b.LocalId equals f.Id into bf
                    from f in bf.DefaultIfEmpty()
                    select @select.Invoke(a, b, c, d, e, f);
            if (search.IsBecode == false)
            {
                q = q.Where(x => x.IsBecode == false);
            }
            else
            {
                q = q.Where(x => x.IsBecode == true);
            }
            var where = LinqHelper.True<PB_MaterialStockDTO>();
            where = where.And(u => u.Nums > 0);
            //筛选
            if (!search.Keyword.IsNullOrEmpty())
            {
                var newWhere = DynamicExpressionParser.ParseLambda<PB_MaterialStockDTO, bool>(
                    ParsingConfig.Default, false, $@"MaterialName.Contains(@0) or MaterialCode.Contains(@0) or SimpleName.Contains(@0)", search.Keyword);
                where = where.And(newWhere);
            }
            if (!search.MaterialTypeId.IsNullOrEmpty())
            {
                where = where.And(x => x.MaterialTypeId == search.MaterialTypeId);
            }
            if (!search.StorId.IsNullOrEmpty())
            {
                where = where.And(x => x.StorId == search.StorId);
            }
            return await q.Where(where).OrderBy(x => x.MaterialCode).GetPageResultAsync(input);
        }
        public async Task<PB_Material> GetByBarcode(string code)
        {
            return await this.GetIQueryable().SingleOrDefaultAsync(w => w.BarCode == code);
        }

        public async Task<List<PB_MaterialDTO>> QueryDataQueryAsync(string MId, string id)
        {
            Expression<Func<PB_Material, PB_MaterialType, PB_DesignDetail, Base_UnitGroup, PB_MaterialDTO>> select = (a, b, c, d) => new PB_MaterialDTO
            {
                MaterialID = a.Id,//物料编号
                MaterialName = a.Name,//物料名称
                MaterialTypeId = b.Name,//类型名称
                MaterialCode = a.Code,//物料编号
                Weight = c.Weight,//理论重量
                TypeID = b.Id,
                RealNums = c.RealNums,
                Nums = c.Nums,
                AParentId = c.ParentId,
                UnitGroupName = d.Name,
                DRemarks = c.Remarks,
                VersionNumber = c.VersionNumber,
                DesignDetailId = c.Id,
                ReplaceId = c.ReplaceId,
                State = c.State,
                Slot =c.Slot
            };
            select = select.BuildExtendSelectExpre();
            var q = from a in GetIQueryable().AsExpandable()
                    join b in Db.GetIQueryable<PB_MaterialType>() on a.MaterialTypeId equals b.Id into ab
                    from b in ab.DefaultIfEmpty()
                    join c in Db.GetIQueryable<PB_DesignDetail>() on a.Id equals c.MaterialId into ac
                    from c in ac.DefaultIfEmpty()
                    join d in Db.GetIQueryable<Base_UnitGroup>() on a.UnitGroupId equals d.Id into ad
                    from d in ad.DefaultIfEmpty()
                    select @select.Invoke(a, b, c, d);
            //筛选
            q = q.OrderBy(m=>m.Slot);
            if (!MId.IsNullOrEmpty() && !id.IsNullOrEmpty())
            {
                q = q.Where(w => MId.Contains(w.Id) && w.AParentId == id && w.State == 0);
            }
            else
            {
                return null;
            }
            return await q.ToListAsync();
        }




        public async Task<List<PB_MaterialDTO>> QueryDataQueryDeliverAsync(string MId, string id)
        {
            Expression<Func<PB_Material, PB_MaterialType, PB_ShippingList, Base_UnitGroup, PB_DesignDetail, PB_MaterialDTO>> select = (a, b, c, d, e) => new PB_MaterialDTO
            {
                MaterialID = a.Id,//物料编号
                MaterialName = a.Name,//物料名称
                MaterialTypeId = b.Name,//类型名称
                MaterialCode = a.Code,//物料编号
                Weight = c.Weight,//理论重量
                TypeID = b.Id,
                RealNums = e.RealNums,
                EstimatedQuantity = c.EstimatedQuantity == null ? 0 : c.EstimatedQuantity,
                ShippedNumber = 0,
                Nums = e.Nums,
                AParentId = e.ParentId,
                UnitGroupName = d.Name,
                DRemarks = c.Remarks,
                DesignDetailId = c.Id,
                SubId = e.Id
            };
            select = select.BuildExtendSelectExpre();
            var q = from a in GetIQueryable().AsExpandable()
                    join b in Db.GetIQueryable<PB_MaterialType>() on a.MaterialTypeId equals b.Id into ab
                    from b in ab.DefaultIfEmpty()
                    join c in Db.GetIQueryable<PB_ShippingList>() on a.Id equals c.MaterialId into ac
                    from c in ac.DefaultIfEmpty()
                    join d in Db.GetIQueryable<Base_UnitGroup>() on a.UnitGroupId equals d.Id into ad
                    from d in ad.DefaultIfEmpty()
                    join e in Db.GetIQueryable<PB_DesignDetail>() on a.Id equals e.MaterialId into ae
                    from e in ae.DefaultIfEmpty()
                    select @select.Invoke(a, b, c, d, e);
            //筛选
            if (!MId.IsNullOrEmpty() && !id.IsNullOrEmpty())
            {
                q = q.Where(w => MId.Contains(w.Id) && w.AParentId == id);
            }
            else
            {
                return null;
            }
            return await q.ToListAsync();
        }

        /// <summary>
        /// 流水号加1
        /// </summary>
        /// <param name="QrCode"></param>
        /// <returns></returns>
        private string GetCodeNum(string QrCode)
        {
            string number = "";
            if (!QrCode.IsNullOrEmpty())
            {
                string[] arr = QrCode.Split(',');
                number = arr[arr.Length - 1];
                if (number == "99999")
                {
                    number = GetNum(number);
                }
                else if (Regex.Matches(number, "[A-Z]").Count > 0)
                {
                    string letter = number.Substring(0, 1);
                    string rnumber = number.Substring(number.Length - 4, 4);
                    if (rnumber == "9999")
                    {
                        number = GetNum(number);
                    }
                    else
                    {
                        number = letter + "0000" + (Convert.ToInt32(rnumber) + 1).ToString();
                        number = number.Substring(number.Length - 5, 5);
                    }
                }
                else if (Convert.ToInt32(number) < 99999)
                {
                    number = "00000" + (Convert.ToInt32(number) + 1).ToString();
                    number = number.Substring(number.Length - 5, 5);
                }
            }
            else
            {
                number = "00001";
            }
            return number;
        }

        /// <summary>
        /// 流水号最大值时变化流水号的值
        /// </summary>
        /// <param name="number"></param>
        /// <returns></returns>
        private string GetNum(string number)
        {
            string num = "";
            switch (number)
            {
                case "99999":
                    num = "A0001";
                    break;
                case "A9999":
                    num = "B0001";
                    break;
                case "B9999":
                    num = "C0001";
                    break;
                case "C9999":
                    num = "D0001";
                    break;
                case "D9999":
                    num = "E0001";
                    break;
                case "E9999":
                    num = "F0001";
                    break;
                case "F9999":
                    num = "G0001";
                    break;
                case "G9999":
                    num = "H0001";
                    break;
                case "H9999":
                    num = "I0001";
                    break;
                case "I9999":
                    num = "J0001";
                    break;
                case "J9999":
                    num = "K0001";
                    break;
                case "K9999":
                    num = "L0001";
                    break;
                case "L9999":
                    num = "M0001";
                    break;
                case "M9999":
                    num = "N0001";
                    break;
                case "N9999":
                    num = "O0001";
                    break;
                case "O9999":
                    num = "P0001";
                    break;
                case "P9999":
                    num = "Q0001";
                    break;
                case "Q9999":
                    num = "R0001";
                    break;
                case "R9999":
                    num = "S0001";
                    break;
                case "S9999":
                    num = "U0001";
                    break;
                case "U9999":
                    num = "V0001";
                    break;
                case "V9999":
                    num = "W0001";
                    break;
                case "W9999":
                    num = "X0001";
                    break;
                case "X9999":
                    num = "Y0001";
                    break;
                case "Y9999":
                    num = "Z0001";
                    break;
                default:
                    num = number;
                    break;
            }
            return num;
        }
        public async Task<List<PB_MaterialPricDTOList>> GetDataListDTOAsync(PageInput<ConditionDTO> input)
        {
            //var search = input.Search;
            //Expression<Func<PB_Material, PB_ProcecureSub, PB_MaterialPriceDTO>> select = (a, b) => new PB_MaterialPriceDTO
            //{
            //    Name=a.Name,
            //    UnitPrice = b.UnitPrice,
            //    ProcecureSubidName = b.Name
            //};
            //select = select.BuildExtendSelectExpre();
            //var q = from a in GetIQueryable().AsExpandable()
            //        join b in Db.GetIQueryable<PB_ProcecureSub>() on a.ProcecureId equals b.Id 
            //        //into ab
            //        //from b in ab.DefaultIfEmpty()
            //        select @select.Invoke(a, b);
            ////筛选
            //if (!search.Condition.IsNullOrEmpty() && !search.Keyword.IsNullOrEmpty())
            //{
            //    var keyword = $"%{search.Keyword}%";
            //    q = q.Where(x => EF.Functions.Like(search.Condition, keyword));
            //}
            // return await q.GetPageResultAsync(input);

            var res = Db.GetListBySql<PB_MaterialPriceDTO>(@"SELECT b.`Name`,a.UnitPrice,a.`Name` ProcecureSubidName  FROM PB_ProcecureSub a INNER JOIN 
 (
	SELECT a.NAME,SUBSTRING_INDEX(SUBSTRING_INDEX(a.ProcecureId,',',b.help_topic_id+1),',',-1) as ProcecureIdInfo  
 from PB_Material a left join mysql.help_topic b 
 on b.help_topic_id < (LENGTH(a.ProcecureId)-LENGTH(REPLACE(a.ProcecureId,',',''))+1) 
 ORDER BY a.id
 ) b on a.Id=b.ProcecureIdInfo
		"); ;


            #region   分页处理数据   有bug 没有按照主数据进行处理
            //进行数据分页
            //    public int pagesize = 12;//每一页显示数据的条数，在这里让每一页显示12条
            //public int pageindex;//记录当前的页数为第几页
            int pagecount;//总页数
            List<PB_MaterialPriceDTO> listpro_down = res.ToList();//一个要分页的，类型数据集合，存储的是所有的数据
            List<PB_MaterialPriceDTO> productes = new List<PB_MaterialPriceDTO>();//用来存储要展示的数据
            int pagees = listpro_down.Count;//所有数据的总条数
                                            //计算总页数
            if (pagees % input.PageRows == 0)
            {
                pagecount = pagees / input.PageRows;
            }
            else
            {
                pagecount = (pagees / input.PageRows) + 1;
            }
            productes = listpro_down.Skip((input.PageIndex - 1) * input.PageRows).Take(input.PageRows).ToList();//分页的重点
            #endregion
            List<PB_MaterialPricDTOList> dataTypeInfos = new List<PB_MaterialPricDTOList>();
            List<IGrouping<string, PB_MaterialPriceDTO>> fenzu = productes.GroupBy(P => P.Name).ToList();
            foreach (IGrouping<string, PB_MaterialPriceDTO> item in fenzu)
            {
                PB_MaterialPricDTOList dataTypeInfo = new PB_MaterialPricDTOList();
                dataTypeInfo.MaterialName = item.Key;
                dataTypeInfo.DataLength = pagees;
                dataTypeInfo.Sub = item.ToList();
                dataTypeInfos.Add(dataTypeInfo);
            }
            return dataTypeInfos;
        }



        public async Task<List<PB_MaterialListDTO>> Selectid(string id)
        {
            Expression<Func<PB_Material, PB_MaterialType, Base_UnitGroup, PB_ProcecureSub, PB_MaterialListDTO>> select = (a, b, c, d) => new PB_MaterialListDTO
            {
                MaterialTypeName = b.Name,
                UnitGroupName = c.Name,
                ProcecureName = d.Name
            };
            select = select.BuildExtendSelectExpre();
            var q = from a in GetIQueryable().AsExpandable()
                    join b in Db.GetIQueryable<PB_MaterialType>() on a.MaterialTypeId equals b.Id into ab
                    from b in ab.DefaultIfEmpty()
                    join c in Db.GetIQueryable<Base_UnitGroup>() on a.UnitGroupId equals c.Id into ac
                    from c in ac.DefaultIfEmpty()
                    join d in Db.GetIQueryable<PB_ProcecureSub>() on a.ProcecureId equals d.Id into ad
                    from d in ad.DefaultIfEmpty()
                    select @select.Invoke(a, b, c, d);
            string[] arr = id.Split(',');
            List<string> List = new List<string>();
            foreach (var item in arr)
            {
                List.Add(item);
            }
            q = q.Where(m => List.Contains(m.Id));
            return await q.ToListAsync();
        }

        public async Task<PageResult<PB_MaterialStockDTO>> GetMaterialStockDataListAsyncs(PageInput<PBMaterialConditionDTO> input)
        {
            var search = input.Search;
            Expression<Func< IT_LocalMaterial,PB_Material, Base_Department,PB_Location,PB_Rack, PB_StorArea,PB_MaterialStockDTO>> select = (b,a, c, f,e,d) => new PB_MaterialStockDTO
            {
                //Ids = b.Id,
                MaterialName = a.Name,
                MaterialCode = a.Code,
                MaterialSpec = a.Spec,
                SimpleName = a.SimpleName,
                MaterialTypeId = a.MaterialTypeId,
                IsBecode = a.IsBecode,
                Id = b.Id,
                MaterialId = b.MaterialId,
                Nums = b.Nums_N + b.Nums_O - b.Nums_R,
                Warehouse = c.Name,
                StorId = c.Id,
                Area = d.Name,
                AreaId = d.Id,
                Rack = e.Name,
                RackId = e.Id,
                Location = f.Name,
                LocalId = f.Id,
                AreaName = d.Name,
                RackName = e.Name,
                LocationName = f.Name,


            };
            select = select.BuildExtendSelectExpre();
            
            var q = from b in Db.GetIQueryable<IT_LocalMaterial>().AsExpandable()
                    join a in Db.GetIQueryable<PB_Material>() on b.MaterialId  equals a.Id into ba
                    from a in ba.DefaultIfEmpty()
                    join c in Db.GetIQueryable<Base_Department>() on b.StorId equals c.Id into bc
                    from c in bc.DefaultIfEmpty()
                    join f in Db.GetIQueryable<PB_Location>() on b.LocalId equals f.Id into bf
                    from f in bf.DefaultIfEmpty()
                    join e in Db.GetIQueryable<PB_Rack>() on f.RackId equals e.Id into fe
                    from e in fe.DefaultIfEmpty()
                    join d in Db.GetIQueryable<PB_StorArea>() on e.AreaId equals d.Id into ed
                    from d in ed.DefaultIfEmpty()
                    
                    
                    select @select.Invoke(b,a, c,f,e,d);
            if (search.IsBecode == false)
            {
                q = q.Where(x => x.IsBecode == false);
            }
            else
            {
                q = q.Where(x => x.IsBecode == true);
            }
            var where = LinqHelper.True<PB_MaterialStockDTO>();
            where = where.And(u => u.Nums > 0);
            //筛选
            if (!search.Keyword.IsNullOrEmpty())
            {
                var newWhere = DynamicExpressionParser.ParseLambda<PB_MaterialStockDTO, bool>(
                    ParsingConfig.Default, false, $@"MaterialName.Contains(@0) or MaterialCode.Contains(@0) or SimpleName.Contains(@0)", search.Keyword);
                where = where.And(newWhere);
            }
            if (!search.MaterialTypeId.IsNullOrEmpty())
            {
                where = where.And(x => x.MaterialTypeId == search.MaterialTypeId);
            }
            if (!search.StorId.IsNullOrEmpty())
            {
                where = where.And(x => x.StorId == search.StorId);
            }
            return await q.Where(where).OrderBy(x => x.MaterialCode).GetPageResultAsync(input);
        }

        /// <summary>
        /// 设计清单导入获取物料分类信息及物料code
        /// </summary>
        /// <returns></returns>
        public DesignListMaterialTypeDTO GetDesignListMaterialTypeAsync()
        {
            string sqlMain = @"SELECT Id,(select Max(`Code`) from PB_Material where MaterialTypeId=PB_MaterialType.Id  and `Code` LIKE '16%' ) as MaterialCode FROM PB_MaterialType where `Code`='16'";
            var data = Db.GetDataTableWithSql(sqlMain);

            DesignListMaterialTypeDTO mtdata = new DesignListMaterialTypeDTO();
            mtdata.Id = data.Rows[0][0].ToString();
            if (data.Rows[0][1].ToString().IsNullOrEmpty())
            {
                mtdata.MaterialCode = "";
            }
            else
            {
                mtdata.MaterialCode = data.Rows[0][1].ToString().Split('.')[1].ToString();
             }
            return mtdata;
        }

        public async Task<PB_Material> GetMaterId(string Id)
        {
            var pb_Material = await Db.GetIQueryable<PB_Material>().Where(m => m.Id == Id).FirstOrDefaultAsync();
            return pb_Material;
        }

        /// <summary>
        /// 物料档案选择隶属成品
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<PageResult<PB_Material>> GetMaterialProductListAsync(PageInput<PBMaterialConditionDTO> input)
        {
            var q = GetIQueryable();
            var where = LinqHelper.True<PB_Material>();
            var search = input.Search;

            //筛选
            if (!search.Keyword.IsNullOrEmpty())
            {
                var newWhere = DynamicExpressionParser.ParseLambda<PB_Material, bool>(
                    ParsingConfig.Default, false, $@"Name.Contains(@0) or Code.Contains(@0) or SimpleName.Contains(@0)", search.Keyword);
                where = where.And(newWhere);
            }
            if (!search.MaterialTypeId.IsNullOrEmpty())
            {
                where = where.And(x => x.MaterialTypeId == search.MaterialTypeId);
            }
            //只显示成品
            where = where.And(x => x.IsProduct == true);

            return await q.Where(where).GetPageResultAsync(input);
        }

        public async Task UpdateMaterialUsageStatus(UpdateUsageStatus data)
        {
            switch (data.state)
            {
                case true:
                    string uopdatetrue = "update PB_Material SET UsageStatus=true WHERE Id='"+data.Id+"'";
                    await Db.ExecuteSqlAsync(uopdatetrue);
                    break;
                case false:
                    string updatefalse = "update PB_Material SET UsageStatus=false WHERE Id='"+data.Id+"'";
                    await Db.ExecuteSqlAsync(updatefalse);
                    break;

             
            }
        }

        #endregion

        #region 私有成员

        #endregion

        #region 物料轨迹跟踪

        public async Task<PageResult<MaterialTrajectoryDTO>> GetMaterialTrajectoryListAsync(PageInput<MTConditionDTO> input)
        {
            var search = input.Search;
            List<MaterialTrajectoryDTO> listdata = new List<MaterialTrajectoryDTO>();
            //var materialmodel = search.MaterialCode.Split(',');
            if (search.MaterialCode.IsNullOrEmpty())
            {
                throw new BusException("请输入的物料名称/编码/条码编码");
            }
            List<string> materilaids = new List<string>();
            string matercode = string.Empty;
            PB_JobBookingQrCode jobdata = await Db.GetIQueryable<PB_JobBookingQrCode>().Where(x => x.Id == search.MaterialCode).FirstOrDefaultAsync();
            if (!jobdata.IsNullOrEmpty())
            {
                //matercode = jobdata.MaterialId;
                materilaids.Add(jobdata.MaterialId);
            }
            else
            {
                List<PB_Material> Material = await Db.GetIQueryable<PB_Material>().Where(x => x.Code.Contains(search.MaterialCode) || x.Name.Contains(search.MaterialCode) || x.Id.Contains(search.MaterialCode)).ToListAsync();
                foreach (var item in Material)
                {
                    materilaids.Add(item.Id);
                }
                //matercode = Material.Id;
            }
            if (materilaids.Count == 0)
            {
                throw new BusException("请检查输入的物料名称/编码/条码编码是否正确");
            }
            matercode = string.Join("','", materilaids.ToArray());

            int i = 0;

            #region 档案建立-1
            Expression<Func<PB_Material, PB_MaterialType, Base_UnitItems, Base_User, MaterialTrajectoryDTO>> select = (a, b, c, d) => new MaterialTrajectoryDTO
            {
                //RowLine = 1,
                MaterialId = a.Id,
                MaterialCode = a.Code,
                MaterialName = a.Name,
                MaterialSpec = a.Spec,
                MaterialTypeId = a.MaterialTypeId,
                MaterialTypeName = b.Name,
                StorageName = c.Name,
                Weight = a.Weight,
                BusinessDate = a.CreateTime,
                Type = "档案建立",
                RealName = d.RealName,
                BusinessType = 1
            };
            select = select.BuildExtendSelectExpre();
            var q = from a in Db.GetIQueryable<PB_Material>().AsExpandable()
                    join b in Db.GetIQueryable<PB_MaterialType>() on a.MaterialTypeId equals b.Id into ab
                    from b in ab.DefaultIfEmpty()
                    join c in Db.GetIQueryable<Base_UnitItems>() on a.storageId equals c.Id into ac
                    from c in ac.DefaultIfEmpty()
                    join d in Db.GetIQueryable<Base_User>() on a.CreatorId equals d.Id into ad
                    from d in ad.DefaultIfEmpty()
                    select @select.Invoke(a, b, c, d);
            var materialdata = await q.Where(x => materilaids.Contains(x.MaterialId)).ToListAsync();
            foreach (var item in materialdata)
            {
                i++;
                item.RowLine = i;
                listdata.Add(item);
            }
            //listdata.Add(materialdata);
            #endregion

            

            #region 入库-2

            string instorsql = $@"select b.MaterialId,c.`Code` as MaterialCode,c.`Name` as MaterialName,c.Spec as MaterialSpec,c.MaterialTypeId,e.`Name` as MaterialTypeName,f.`Name` as StorageName,(c.Weight*SUM(b.Num)) as Weight,a.InStorTime as BusinessDate,d.`Name` as Type,SUM(b.Num) as Nums,a.Id as DocumentNo,g.RealName from td_instorage as a
left join td_instordetail as b on a.Id=b.InStorId
left join pb_material as c on b.MaterialId=c.Id
left join pb_materialtype as e on c.MaterialTypeId=e.Id
left join base_unititems as f on c.storageId=f.Id
left join (select * from base_enumitem where EnumCode='InStorageType') as d on a.InType=d.`Code` 
left join base_user as g on a.CreatorId=g.Id
where b.MaterialId in ('{matercode}')
GROUP BY b.MaterialId,c.`Code`,c.`Name`,c.Spec,c.MaterialTypeId,e.`Name`,f.`Name`,a.InStorTime,d.`Name`,a.Id,g.RealName";
            var instorList = await Db.GetDataTableWithSqlAsync(instorsql);
            foreach (DataRow dr in instorList.Rows)
            {
                i++;
                MaterialTrajectoryDTO mtdata = new MaterialTrajectoryDTO();
                mtdata.RowLine = i;
                mtdata.MaterialId = dr["MaterialId"].ToString();
                mtdata.MaterialCode = dr["MaterialCode"].ToString();
                mtdata.MaterialName = dr["MaterialName"].ToString();
                mtdata.MaterialSpec = dr["MaterialSpec"].ToString();
                mtdata.MaterialTypeId = dr["MaterialTypeId"].ToString();
                mtdata.MaterialTypeName = dr["MaterialTypeName"].ToString();
                mtdata.StorageName = dr["StorageName"].ToString();
                mtdata.Weight = Convert.ToDouble(dr["Weight"]);
                mtdata.BusinessDate = Convert.ToDateTime(dr["BusinessDate"]);
                mtdata.Type = dr["Type"].ToString();
                mtdata.Nums = Convert.ToDouble(dr["Nums"]);
                mtdata.DocumentNo = dr["DocumentNo"].ToString();
                mtdata.RealName = dr["RealName"].ToString();
                mtdata.BusinessType = 2;
                listdata.Add(mtdata);
            }
            #endregion

            #region 调拨-3

            string allocatesql = $@"select b.MaterialId,c.`Code` as MaterialCode,c.`Name` as MaterialName,c.Spec as MaterialSpec,c.MaterialTypeId,e.`Name` as MaterialTypeName,f.`Name` as StorageName,(c.Weight*SUM(b.PackNums)) as Weight,a.AllocateTime as BusinessDate,'调拨' as Type,SUM(b.PackNums) as Nums,a.Id as DocumentNo,g.RealName from td_allocate as a
left join td_allocatepackdetail as b on a.Id=b.AllocateId and b.`Status`=1
left join pb_material as c on b.MaterialId=c.Id
left join pb_materialtype as e on c.MaterialTypeId=e.Id
left join base_unititems as f on c.storageId=f.Id
left join base_user as g on a.CreatorId=g.Id
where b.MaterialId in ('{matercode}')
GROUP BY b.MaterialId,c.`Code`,c.`Name`,c.Spec,c.MaterialTypeId,e.`Name`,f.`Name`,a.AllocateTime,a.Id,g.RealName";
            var llocateList = await Db.GetDataTableWithSqlAsync(allocatesql);
            foreach (DataRow dr in llocateList.Rows)
            {
                i++;
                MaterialTrajectoryDTO mtdata = new MaterialTrajectoryDTO();
                mtdata.RowLine = i;
                mtdata.MaterialId = dr["MaterialId"].ToString();
                mtdata.MaterialCode = dr["MaterialCode"].ToString();
                mtdata.MaterialName = dr["MaterialName"].ToString();
                mtdata.MaterialSpec = dr["MaterialSpec"].ToString();
                mtdata.MaterialTypeId = dr["MaterialTypeId"].ToString();
                mtdata.MaterialTypeName = dr["MaterialTypeName"].ToString();
                mtdata.StorageName = dr["StorageName"].ToString();
                mtdata.Weight = Convert.ToDouble(dr["Weight"]);
                mtdata.BusinessDate = Convert.ToDateTime(dr["BusinessDate"]);
                mtdata.Type = dr["Type"].ToString();
                mtdata.Nums = Convert.ToDouble(dr["Nums"]);
                mtdata.DocumentNo = dr["DocumentNo"].ToString();
                mtdata.RealName = dr["RealName"].ToString();
                mtdata.BusinessType = 3;
                listdata.Add(mtdata);
            }
            #endregion

            #region 移库-4

            string movesql = $@"select b.MaterialId,c.`Code` as MaterialCode,c.`Name` as MaterialName,c.Spec as MaterialSpec,c.MaterialTypeId,e.`Name` as MaterialTypeName,f.`Name` as StorageName,(c.Weight*SUM(b.MoveNum)) as Weight,a.MoveTime as BusinessDate,'移库' as Type,SUM(b.MoveNum) as Nums,a.Id as DocumentNo,g.RealName from td_move as a
left join  td_movedetail as b on a.Id=b.MoveId
left join pb_material as c on b.MaterialId=c.Id
left join pb_materialtype as e on c.MaterialTypeId=e.Id
left join base_unititems as f on c.storageId=f.Id
left join base_user as g on a.CreatorId=g.Id
where b.MaterialId in ('{matercode}')
GROUP BY b.MaterialId,c.`Code`,c.`Name`,c.Spec,c.MaterialTypeId,e.`Name`,f.`Name`,a.MoveTime,a.Id,g.RealName";
            var moveList = await Db.GetDataTableWithSqlAsync(allocatesql);
            foreach (DataRow dr in moveList.Rows)
            {
                i++;
                MaterialTrajectoryDTO mtdata = new MaterialTrajectoryDTO();
                mtdata.RowLine = i;
                mtdata.MaterialId = dr["MaterialId"].ToString();
                mtdata.MaterialCode = dr["MaterialCode"].ToString();
                mtdata.MaterialName = dr["MaterialName"].ToString();
                mtdata.MaterialSpec = dr["MaterialSpec"].ToString();
                mtdata.MaterialTypeId = dr["MaterialTypeId"].ToString();
                mtdata.MaterialTypeName = dr["MaterialTypeName"].ToString();
                mtdata.StorageName = dr["StorageName"].ToString();
                mtdata.Weight = Convert.ToDouble(dr["Weight"]);
                mtdata.BusinessDate = Convert.ToDateTime(dr["BusinessDate"]);
                mtdata.Type = dr["Type"].ToString();
                mtdata.Nums = Convert.ToDouble(dr["Nums"]);
                mtdata.DocumentNo = dr["DocumentNo"].ToString();
                mtdata.RealName = dr["RealName"].ToString();
                mtdata.BusinessType = 4;
                listdata.Add(mtdata);
            }
            #endregion

            #region 盘点-5

            string checksql = $@"select b.MaterialId,c.`Code` as MaterialCode,c.`Name` as MaterialName,c.Spec as MaterialSpec,c.MaterialTypeId,e.`Name` as MaterialTypeName,f.`Name` as StorageName,(c.Weight*SUM(b.Nums)) as Weight,a.CheckTime as BusinessDate,'盘点' as Type,SUM(b.Nums) as Nums,a.Id as DocumentNo,g.RealName from td_check as a
left join td_checkpack as b on a.Id=b.CheckId
left join pb_material as c on b.MaterialId=c.Id
left join pb_materialtype as e on c.MaterialTypeId=e.Id
left join base_unititems as f on c.storageId=f.Id
left join base_user as g on a.CreatorId=g.Id
where b.MaterialId in ('{matercode}')
GROUP BY b.MaterialId,c.`Code`,c.`Name`,c.Spec,c.MaterialTypeId,e.`Name`,f.`Name`,a.CheckTime,a.Id,g.RealName";
            var checkList = await Db.GetDataTableWithSqlAsync(allocatesql);
            foreach (DataRow dr in checkList.Rows)
            {
                i++;
                MaterialTrajectoryDTO mtdata = new MaterialTrajectoryDTO();
                mtdata.RowLine = i;
                mtdata.MaterialId = dr["MaterialId"].ToString();
                mtdata.MaterialCode = dr["MaterialCode"].ToString();
                mtdata.MaterialName = dr["MaterialName"].ToString();
                mtdata.MaterialSpec = dr["MaterialSpec"].ToString();
                mtdata.MaterialTypeId = dr["MaterialTypeId"].ToString();
                mtdata.MaterialTypeName = dr["MaterialTypeName"].ToString();
                mtdata.StorageName = dr["StorageName"].ToString();
                mtdata.Weight = Convert.ToDouble(dr["Weight"]);
                mtdata.BusinessDate = Convert.ToDateTime(dr["BusinessDate"]);
                mtdata.Type = dr["Type"].ToString();
                mtdata.Nums = Convert.ToDouble(dr["Nums"]);
                mtdata.DocumentNo = dr["DocumentNo"].ToString();
                mtdata.RealName = dr["RealName"].ToString();
                mtdata.BusinessType = 5;
                listdata.Add(mtdata);
            }
            #endregion

            #region 损益-6

            string badsql = $@"select b.MaterialId,c.`Code` as MaterialCode,c.`Name` as MaterialName,c.Spec as MaterialSpec,c.MaterialTypeId,e.`Name` as MaterialTypeName,f.`Name` as StorageName,(c.Weight*SUM(b.BadNum)) as Weight,a.BadTime as BusinessDate,'损益' as Type,SUM(b.BadNum) as Nums,a.Id as DocumentNo,g.RealName from td_bad as a
left join td_baddetail as b on a.Id=b.BadId
left join pb_material as c on b.MaterialId=c.Id
left join pb_materialtype as e on c.MaterialTypeId=e.Id
left join base_unititems as f on c.storageId=f.Id
left join base_user as g on a.CreatorId=g.Id
where b.MaterialId in ('{matercode}')
GROUP BY b.MaterialId,c.`Code`,c.`Name`,c.Spec,c.MaterialTypeId,e.`Name`,f.`Name`,a.BadTime,a.Id,g.RealName";
            var badList = await Db.GetDataTableWithSqlAsync(allocatesql);
            foreach (DataRow dr in badList.Rows)
            {
                i++;
                MaterialTrajectoryDTO mtdata = new MaterialTrajectoryDTO();
                mtdata.RowLine = i;
                mtdata.MaterialId = dr["MaterialId"].ToString();
                mtdata.MaterialCode = dr["MaterialCode"].ToString();
                mtdata.MaterialName = dr["MaterialName"].ToString();
                mtdata.MaterialSpec = dr["MaterialSpec"].ToString();
                mtdata.MaterialTypeId = dr["MaterialTypeId"].ToString();
                mtdata.MaterialTypeName = dr["MaterialTypeName"].ToString();
                mtdata.StorageName = dr["StorageName"].ToString();
                mtdata.Weight = Convert.ToDouble(dr["Weight"]);
                mtdata.BusinessDate = Convert.ToDateTime(dr["BusinessDate"]);
                mtdata.Type = dr["Type"].ToString();
                mtdata.Nums = Convert.ToDouble(dr["Nums"]);
                mtdata.DocumentNo = dr["DocumentNo"].ToString();
                mtdata.RealName = dr["RealName"].ToString();
                mtdata.BusinessType = 6;
                listdata.Add(mtdata);
            }
            #endregion

            #region 出库-7

            string outstorsql = $@"select b.MaterialId,c.`Code` as MaterialCode,c.`Name` as MaterialName,c.Spec as MaterialSpec,c.MaterialTypeId,e.`Name` as MaterialTypeName,f.`Name` as StorageName,(c.Weight*SUM(b.OutNum)) as Weight,a.OutTime as BusinessDate,d.`Name` as Type,SUM(b.OutNum) as Nums,a.Id as DocumentNo,g.RealName from td_outstorage as a
left join td_outstordetail as b on a.Id=b.OutStorId
left join pb_material as c on b.MaterialId=c.Id
left join pb_materialtype as e on c.MaterialTypeId=e.Id
left join base_unititems as f on c.storageId=f.Id
left join (select * from base_enumitem where EnumCode='OutStorageType') as d on a.OutType=d.`Code` 
left join base_user as g on a.CreatorId=g.Id
where b.MaterialId in ('{matercode}')
GROUP BY b.MaterialId,c.`Code`,c.`Name`,c.Spec,c.MaterialTypeId,e.`Name`,f.`Name`,a.OutTime,d.`Name`,a.Id,g.RealName";
            var outstorList = await Db.GetDataTableWithSqlAsync(allocatesql);
            foreach (DataRow dr in outstorList.Rows)
            {
                i++;
                MaterialTrajectoryDTO mtdata = new MaterialTrajectoryDTO();
                mtdata.RowLine = i;
                mtdata.MaterialId = dr["MaterialId"].ToString();
                mtdata.MaterialCode = dr["MaterialCode"].ToString();
                mtdata.MaterialName = dr["MaterialName"].ToString();
                mtdata.MaterialSpec = dr["MaterialSpec"].ToString();
                mtdata.MaterialTypeId = dr["MaterialTypeId"].ToString();
                mtdata.MaterialTypeName = dr["MaterialTypeName"].ToString();
                mtdata.StorageName = dr["StorageName"].ToString();
                mtdata.Weight = Convert.ToDouble(dr["Weight"]);
                mtdata.BusinessDate = Convert.ToDateTime(dr["BusinessDate"]);
                mtdata.Type = dr["Type"].ToString();
                mtdata.Nums = Convert.ToDouble(dr["Nums"]);
                mtdata.DocumentNo = dr["DocumentNo"].ToString();
                mtdata.RealName = dr["RealName"].ToString();
                mtdata.BusinessType = 7;
                listdata.Add(mtdata);
            }
            #endregion

            #region 装车-8

            string billsql = $@"select b.MaterialId,c.`Code` as MaterialCode,c.`Name` as MaterialName,c.Spec as MaterialSpec,c.MaterialTypeId,e.`Name` as MaterialTypeName,f.`Name` as StorageName,(c.Weight*SUM(b.PackNums)) as Weight,a.SendTime as BusinessDate,'装车' as Type,SUM(b.PackNums) as Nums,a.Id as DocumentNo,g.RealName from pb_transportbill as a
left join pb_transportbilldetail as b on a.Id=b.ParentId
left join pb_material as c on b.MaterialId=c.Id
left join pb_materialtype as e on c.MaterialTypeId=e.Id
left join base_unititems as f on c.storageId=f.Id
left join base_user as g on a.CreatorId=g.Id
where b.MaterialId in ('{matercode}')
GROUP BY b.MaterialId,c.`Code`,c.`Name`,c.Spec,c.MaterialTypeId,e.`Name`,f.`Name`,a.SendTime,a.Id,g.RealName";
            var billList = await Db.GetDataTableWithSqlAsync(allocatesql);
            foreach (DataRow dr in billList.Rows)
            {
                i++;
                MaterialTrajectoryDTO mtdata = new MaterialTrajectoryDTO();
                mtdata.RowLine = i;
                mtdata.MaterialId = dr["MaterialId"].ToString();
                mtdata.MaterialCode = dr["MaterialCode"].ToString();
                mtdata.MaterialName = dr["MaterialName"].ToString();
                mtdata.MaterialSpec = dr["MaterialSpec"].ToString();
                mtdata.MaterialTypeId = dr["MaterialTypeId"].ToString();
                mtdata.MaterialTypeName = dr["MaterialTypeName"].ToString();
                mtdata.StorageName = dr["StorageName"].ToString();
                mtdata.Weight = Convert.ToDouble(dr["Weight"]);
                mtdata.BusinessDate = Convert.ToDateTime(dr["BusinessDate"]);
                mtdata.Type = dr["Type"].ToString();
                mtdata.Nums = Convert.ToDouble(dr["Nums"]);
                mtdata.DocumentNo = dr["DocumentNo"].ToString();
                mtdata.RealName = dr["RealName"].ToString();
                mtdata.BusinessType = 8;
                listdata.Add(mtdata);
            }
            #endregion

            #region 收货-9

            string Receiptsql = $@"select b.MaterialId,c.`Code` as MaterialCode,c.`Name` as MaterialName,c.Spec as MaterialSpec,c.MaterialTypeId,e.`Name` as MaterialTypeName,f.`Name` as StorageName,(c.Weight*SUM(b.Nums)) as Weight,a.SendTime as BusinessDate,'收货' as Type,SUM(b.Nums) as Nums,a.Id as DocumentNo,g.RealName from pb_transportbill as a
left join pb_tranrecevingdetail as b on a.Id=b.BillId
left join pb_material as c on b.MaterialId=c.Id
left join pb_materialtype as e on c.MaterialTypeId=e.Id
left join base_unititems as f on c.storageId=f.Id
left join base_user as g on a.CreatorId=g.Id
where b.MaterialId in ('{matercode}')
GROUP BY b.MaterialId,c.`Code`,c.`Name`,c.Spec,c.MaterialTypeId,e.`Name`,f.`Name`,a.SendTime,a.Id,g.RealName";
            var ReceiptList = await Db.GetDataTableWithSqlAsync(allocatesql);
            foreach (DataRow dr in ReceiptList.Rows)
            {
                i++;
                MaterialTrajectoryDTO mtdata = new MaterialTrajectoryDTO();
                mtdata.RowLine = i;
                mtdata.MaterialId = dr["MaterialId"].ToString();
                mtdata.MaterialCode = dr["MaterialCode"].ToString();
                mtdata.MaterialName = dr["MaterialName"].ToString();
                mtdata.MaterialSpec = dr["MaterialSpec"].ToString();
                mtdata.MaterialTypeId = dr["MaterialTypeId"].ToString();
                mtdata.MaterialTypeName = dr["MaterialTypeName"].ToString();
                mtdata.StorageName = dr["StorageName"].ToString();
                mtdata.Weight = Convert.ToDouble(dr["Weight"]);
                mtdata.BusinessDate = Convert.ToDateTime(dr["BusinessDate"]);
                mtdata.Type = dr["Type"].ToString();
                mtdata.Nums = Convert.ToDouble(dr["Nums"]);
                mtdata.DocumentNo = dr["DocumentNo"].ToString();
                mtdata.RealName = dr["RealName"].ToString();
                mtdata.BusinessType = 9;
                listdata.Add(mtdata);
            }
            #endregion

            #region 回货分拣-10

            string sortingsql = $@"select b.MaterialId,c.`Code` as MaterialCode,c.`Name` as MaterialName,c.Spec as MaterialSpec,c.MaterialTypeId,e.`Name` as MaterialTypeName,f.`Name` as StorageName,(c.Weight*SUM(b.SortingSum)) as Weight,a.ReturnDate as BusinessDate,'回货分拣' as Type,SUM(b.SortingSum) as Nums,a.ParentId as DocumentNo,g.RealName from pb_returnsorting as a
left join pb_returnsortdetail as b on a.Id=b.ParentId
left join pb_material as c on b.MaterialId=c.Id
left join pb_materialtype as e on c.MaterialTypeId=e.Id
left join base_unititems as f on c.storageId=f.Id
left join base_user as g on a.CreatorId=g.Id
where b.MaterialId in ('{matercode}')
GROUP BY b.MaterialId,c.`Code`,c.`Name`,c.Spec,c.MaterialTypeId,e.`Name`,f.`Name`,a.ReturnDate,a.Id,g.RealName";
            var sortingList = await Db.GetDataTableWithSqlAsync(allocatesql);
            foreach (DataRow dr in sortingList.Rows)
            {
                i++;
                MaterialTrajectoryDTO mtdata = new MaterialTrajectoryDTO();
                mtdata.RowLine = i;
                mtdata.MaterialId = dr["MaterialId"].ToString();
                mtdata.MaterialCode = dr["MaterialCode"].ToString();
                mtdata.MaterialName = dr["MaterialName"].ToString();
                mtdata.MaterialSpec = dr["MaterialSpec"].ToString();
                mtdata.MaterialTypeId = dr["MaterialTypeId"].ToString();
                mtdata.MaterialTypeName = dr["MaterialTypeName"].ToString();
                mtdata.StorageName = dr["StorageName"].ToString();
                mtdata.Weight = Convert.ToDouble(dr["Weight"]);
                mtdata.BusinessDate = Convert.ToDateTime(dr["BusinessDate"]);
                mtdata.Type = dr["Type"].ToString();
                mtdata.Nums = Convert.ToDouble(dr["Nums"]);
                mtdata.DocumentNo = dr["DocumentNo"].ToString();
                mtdata.RealName = dr["RealName"].ToString();
                mtdata.BusinessType = 10;
                listdata.Add(mtdata);
            }
            #endregion

            if (!search.StartDate.IsNullOrEmpty() && !search.EndDate.IsNullOrEmpty())
            {
                listdata = listdata.Where(x => x.BusinessDate >= search.StartDate && x.BusinessDate <= search.EndDate).ToList();
            }
            if (!search.RealName.IsNullOrEmpty())
            {
                listdata = listdata.Where(x => x.RealName.Contains(search.RealName)).ToList();
            }
            if (!search.BusinessType.IsNullOrEmpty())
            {
                listdata = listdata.Where(x => x.BusinessType == search.BusinessType).ToList();
            }
            int count = listdata.Count;
            var list = listdata.Skip((input.PageIndex - 1) * input.PageRows).Take(input.PageRows).ToList();
            return new PageResult<MaterialTrajectoryDTO> { Data = list, Total = count };
        }

        #endregion

    }
}